from peewee import *

db = MySQLDatabase('nemtocr', host='localhost', port=3800, user='root', password='!@mtg23YWZ^&')


class OneScoreRank(Model):
    id = PrimaryKeyField()
    year = IntegerField()
    score = IntegerField()
    num = IntegerField()
    total_num = IntegerField()
    rank = IntegerField()
    type = IntegerField()
    is_deleted = IntegerField()

    class Meta:
        database = db  # This model uses the "spider" database.
        table_name = 'one_score_rank'  # 如果不指定，默认是person


    # 插入单条数据(返回新增id)
    def toInsert(self, row):
        try:
            return OneScoreRank.insert(row).execute()
        except Exception as e:
            return ''


    # 批量插入数据
    def toInsertBatch(self, rows):
        with db.atomic():
            OneScoreRank.insert_many(rows).execute()


    # 根据id，获取单条记录
    def getOne(self, id):
        try:
            return OneScoreRank.select().where(OneScoreRank.id == id).get()
        except Exception as e:
            return ''


    # 根据id，获取单条记录
    def getOneByScore(self, score, year, type):
        try:
            query = OneScoreRank.select().where((OneScoreRank.score == score) & (OneScoreRank.year == year) &
                                                (OneScoreRank.type == type)).dicts().get()
            return query
            # query.execute()
        except Exception as e:
            print(e)
            return ''


    # 根据年份查询列表
    def getList(self, year):
        try:
            return OneScoreRank.select().where(OneScoreRank.year == year).order_by(OneScoreRank.id.asc()).dicts().execute()
        except Exception as e:
            return ''


if __name__ == '__main__':
    oneScoreRank = OneScoreRank()
    row = oneScoreRank.getOneByScore(543, 2021, 2)
    print(row)